﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using Server.Tools;

namespace Server.Dao
{
    /// <summary>
    /// 与数据库连接相关的类
    /// </summary>
    public class DBConnection
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string connectionString;

        /// <summary>
        /// 不带数据库访问字符串的构造函数
        /// </summary>
        public DBConnection()
        {
            connectionString = Properties.Settings.Default.ConnectionString;
        }

        /// <summary>
        /// 带数据库访问字符串的构造函数
        /// </summary>
        /// <param name="connectionString">数据库访问字符串</param>
        public DBConnection(string connectionString)
        {
            this.connectionString = connectionString;
        }

        /// <summary>
        /// 用连接字符串构造一个SqlConnection
        /// </summary>
        /// <returns>SqlConnection对象</returns>
        public SqlConnection GetConnection()
        {
            try
            {
				SqlConnectionStringBuilder cSB = new SqlConnectionStringBuilder();
				cSB.ConnectionString = connectionString;
				cSB.AsynchronousProcessing = true;
                SqlConnection conn = new SqlConnection(cSB.ConnectionString);
                return conn;
            }
            catch(SqlException e)
            {
                LogService.Instance.Log.Info("连接数据库失败！" + e.ToString());
                return null;
            }
        }

        /// <summary>
        /// 修改数据库访问字符串
        /// </summary>
        public string ConnectionString
        {
            get { return connectionString; }
            set
            {
                connectionString = value;
            }
        }

        /// <summary>
        /// 尝试连接数据库以检查是否可用
        /// </summary>
        /// <returns>成功则返回true，失败则返回false</returns>
        public bool CheckDBConnection()
        {
            try
            {
                new SqlConnection(connectionString);
                return true;
            }
            catch (Exception e)
            {
                LogService.Instance.Log.Info("连接数据库失败！" + e.ToString());
                return false;
            }
        }

        /// <summary>
        /// 获取SQL语句查询结果，放到DataTable中供接下来使用
        /// （此函数不再推荐使用）
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns>SQL语句查询结果</returns>
        public DataTable GetDataTable(string sql)
        {
            if (sql == string.Empty) return new DataTable();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            DataSet set = new DataSet();
            adapter.Fill(set);
            if (set.Tables.Count > 0)
                return set.Tables[0];
            else
                return new DataTable();
        }

        /// <summary>
        /// 获取SQL语句查询结果，放到DataSet中供接下来使用
        /// （此函数不再推荐使用）
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns>SQL语句查询结果</returns>
        public DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            DataSet set = new DataSet();
            adapter.Fill(set);
            return set;
        }

        /// <summary>
        /// 执行数据库的增删改等操作
        /// （此函数不再推荐使用）
        /// </summary>
        /// <param name="sql">数据库增，删，改等操作的SQL语句或调用存储过程</param>
        /// <returns>SQL语句执行以后所影响的行数</returns>
        public int ExecuteSql(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            int result = -1;
            try
            {
                conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (System.Exception)
            {
                result = -1;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 获取单条记录
        /// （此函数不再推荐使用）
        /// </summary>
        /// <param name="sql">sql查询语句</param>
        /// <returns>SQL语句查询结果</returns>
        public string[] GetStrings(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            string[] res = null;
            SqlDataReader reader = null;
            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    int a = reader.FieldCount;
                    res = new string[a];
                    for (int i = 0; i < a; i++)
                    {
                        res[i] = reader[i].ToString();
                    }
                }
                reader.Close();
            }
            catch (SqlException)
            {
                res = null;
            }
            finally
            {
                conn.Close();
            }
            return res;
        }

        /// <summary>
        /// 获取数据库中单个数据
        /// （此函数不再推荐使用）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>string类型的查询结果</returns>
        public string GetString(string sql)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            string result = "";
            SqlDataReader reader;
            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                reader.Read();
                result = reader[0].ToString();
            }
            catch (System.Exception)
            {
                result = null;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 通过存储过程备份数据库
        /// （此函数不再推荐使用）
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public string BackUp(string storedProcedure, string path)
        {
            string fileName = null;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(storedProcedure, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@path", path);
            cmd.Parameters.Add("@ReturnValue", SqlDbType.VarChar, 200).Direction = ParameterDirection.Output;
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                fileName = (string)cmd.Parameters["@ReturnValue"].Value;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return fileName;
        }
    }
}
